﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class HobbyService
    {
      
        public bool SearchTable()
        {
            SqlConnection conn = ConnPoll.GetConn();
            bool tempbool = false;
            string sql = "select * from Hobby";
            ConnPoll.Open();
            SqlCommand com = new SqlCommand(sql, conn);
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                tempbool = true;
                break;
            }
            dr.Close();
            com = null;
            ConnPoll.Close();
            return tempbool;
        }

        #region 创建兴趣爱好表
        /// <summary>
        /// 创建兴趣爱好表
        /// </summary>
        public void CreateHobby()
        {
            SqlConnection conn = ConnPoll.GetConn();
            try
            {
                string tbna = "Hobby";
                string sql = "use bds249611391_db"
                    + " create table " + tbna
                    + " ("
                    + " HobbyId int not null identity(1,1),"//主键Id
                    + " Explain nvarchar(50) null,"
                    + " HobbyName nvarchar(50) not null"//兴趣爱好名称
                    + " )"
                    + " use bds249611391_db"
                    + " alter table " + tbna
                    + " add constraint PK_HobbyId primary key (HobbyId)";
                ConnPoll.Open();
                SqlCommand com = new SqlCommand(sql,conn);
                com.ExecuteNonQuery();
                com = null;
                ConnPoll.Close();
                Insert(new Hobby() { Explain = "兴趣爱好", HobbyName=string.Empty });
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 查找库中是否已有此内容
        /// <summary>
        /// 查找库中是否已有此内容
        /// </summary>
        /// <param name="h">兴趣爱好表类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Search(Hobby h, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select count(1) from Hobby" + UserName + " where HobbyName=@HobbyName";
            return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text,sql,new SqlParameter("@HobbyName",h.HobbyName)));
        }
        #endregion

        #region 新增信息
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="h">兴趣爱好表类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Insert(Hobby h)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "insert into Hobby (Explain,HobbyName) values (@Explain,@HobbyName)";
            if (h.Explain == string.Empty)
                sql = "insert into Hobby (Explain,HobbyName) values (default,@HobbyName)";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@Explain",h.Explain),
                new SqlParameter("@HobbyName",h.HobbyName)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="h">兴趣爱好表类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Delete(Hobby h)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "delete Hobby where HobbyName=@HobbyName";
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, new SqlParameter("@HobbyName", h.HobbyName));
        }
        #endregion

        #region 修改信息
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="h">兴趣爱好表类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Set(string SetStr, Hobby h)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "update Hobby set HobbyName=@SetStr where HobbyName=@HobbyName";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@SetStr",SetStr),
                new SqlParameter("@HobbyName",h.HobbyName)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text,sql,pars);
        }

        #endregion

        #region 查找信息
        /// <summary>
        /// 查找所有信息
        /// </summary>
        /// <param name="H">兴趣爱好表类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public List<Hobby> SearchAll()
        {
            SqlConnection conn = ConnPoll.GetConn();
            List<Hobby> lh = new List<Hobby>();
            string sql = "select * from Hobby";
            SqlDataReader dr = SQLHelper.ExecuteReader(conn, System.Data.CommandType.Text, sql);
            while (dr.Read())
            {
                if (dr["HobbyName"].ToString() == string.Empty)
                    continue;
                Hobby h = new Hobby();
                h.HobbyId = Convert.ToInt32(dr["HobbyId"]);
                h.HobbyName = dr["HobbyName"].ToString();
                lh.Add(h);
            }
            dr.Close();
            return lh;
        }
        #endregion

        #region 查询标题
        /// <summary>
        /// 查询标题
        /// </summary>
        /// <param name="H"></param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public string SearchTitle()
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select Explain from Hobby";
            return (SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text,sql)).ToString();
        }
        #endregion
    }
}
